package cn.mldn.oracle.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import cn.mldn.oracle.dao.IEmpDAO;
import cn.mldn.oracle.vo.Emp;

public class EmpDAOImpl implements IEmpDAO {
	
	private Connection conn;
	private PreparedStatement pstmt;
	private List<Emp> list;
	
	public EmpDAOImpl(Connection conn) {
		this.conn = conn;
	}

	@Override
	public boolean doCreate(Emp vo) throws Exception {
		String sql = "INSERT INTO emp(empno,ename,job,hiredate,sal,comm) VALUES (?,?,?,?,?,?)";
		pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, vo.getEmpno());
		pstmt.setString(2, vo.getEname());
		pstmt.setString(3, vo.getJob());
		pstmt.setDate(4, new java.sql.Date(vo.getHiredate().getTime()));
		pstmt.setDouble(5, vo.getSal());
		pstmt.setDouble(6, vo.getComm());
		if(pstmt.executeUpdate() > 0){
			return true;
		}
		return false;
	}

	@Override
	public boolean doUpdate(Emp vo) throws Exception {
		String sql = "UPDATE emp SET ename=?,job=?,hiredate=?,sal=?,comm=? WHERE empno=?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, vo.getEname());
		pstmt.setString(2, vo.getJob());
		pstmt.setDate(3, new java.sql.Date(vo.getHiredate().getTime()));
		pstmt.setDouble(4, vo.getSal());
		pstmt.setDouble(5, vo.getComm());
		pstmt.setInt(6, vo.getEmpno());
		if(pstmt.executeUpdate() > 0){
			return true;
		}
		return false;
	}

	@Override
	public boolean doRemove(Integer id) throws Exception {
		String sql = "DELETE FROM emp WHERE empno=?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, id);
		if (pstmt.executeUpdate() > 0) {
			return true;
		}
		return false;
	}

	@Override
	public Emp findById(Integer id) throws Exception {
		String sql = "SELECT empno,ename,job,hiredate,sal,comm FROM emp WHERE empno = ?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, id);
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()) {
			Emp emp = new Emp();
			emp.setEmpno(rs.getInt(1));
			emp.setEname(rs.getString(2));
			emp.setJob(rs.getString(3));
			emp.setHiredate(rs.getDate(4));
			emp.setSal(rs.getDouble(5));
			emp.setComm(rs.getDouble(6));
			return emp;
		}
		return null;
	}

	@Override
	public List<Emp> findAll() throws Exception {
		String sql = "SELECT empno,ename,job,hiredate,sal,comm FROM emp";
		pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()) {
			if (list == null) {
				list = new ArrayList<Emp>();
			}
			Emp emp = new Emp();
			emp.setEmpno(rs.getInt(1));
			emp.setEname(rs.getString(2));
			emp.setJob(rs.getString(3));
			emp.setHiredate(rs.getDate(4));
			emp.setSal(rs.getDouble(5));
			emp.setComm(rs.getDouble(6));
			list.add(emp);
		}
		return list;
	}

	@Override
	public List<Emp> findAll(String column, String keyWord,
			Integer currentPage, Integer lineSize) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Integer getCount(String column, String keyWord) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

}
